# utils/database.py
import pymysql
from UI.config.settings import DATABASE_CONFIG

class Database:
    def __init__(self):
        self.conn = pymysql.connect(**DATABASE_CONFIG)
        self.cursor = self.conn.cursor()
        self.create_table()

    def create_table(self):
        create_table_sql = '''
            CREATE TABLE IF NOT EXISTS users (
                avatar_path TEXT,
                username VARCHAR(255),
                phonenumber VARCHAR(20) PRIMARY KEY,
                account VARCHAR(255) UNIQUE,
                password VARCHAR(255)
            )
        '''
        self.cursor.execute(create_table_sql)
        self.conn.commit()

    def login(self, account, password):
        self.cursor.execute("SELECT * FROM users WHERE (account=%s OR phonenumber=%s) AND password=%s", (account,account, password))
        user = self.cursor.fetchone()
        return user is not None

    def register(self, account, password, username, phonenumber, avatar_path):
        try:
            self.cursor.execute("INSERT INTO users (account, password, username, phonenumber, avatar_path) VALUES (%s, %s,%s,%s,%s)", (account, password, username, phonenumber, avatar_path))
            self.conn.commit()
            return True
        except pymysql.IntegrityError:
            return False

    def reset_password(self, account):
        self.cursor.execute("UPDATE users SET password='123456' WHERE account=%s", (account,))
        if self.cursor.rowcount > 0:
            self.conn.commit()
            return True
        else:
            return False

    def close_connection(self):
        self.conn.close()

    def is_phonenumber_exist(self, phonenumber):
        cursor = self.conn.cursor()
        cursor.execute("SELECT 1 FROM users WHERE phonenumber =%s", (phonenumber,))
        return cursor.fetchone() is not None
    
    def is_account_exist(self, account):
        cursor = self.conn.cursor()
        cursor.execute("SELECT 1 FROM users WHERE account =%s", (account,))
        return cursor.fetchone() is not None

    def get_avatar_path(self, identifier):
        """
        根据账号或手机号获取头像路径
        :param identifier: 账号或手机号
        :return: 头像文件路径 或 None
        """
        cursor = self.conn.cursor()
        try:
            cursor.execute(
                "SELECT avatar_path FROM users WHERE account=%s OR phonenumber=%s",
                (identifier, identifier)
            )
            result = cursor.fetchone()
            return result[0] if result else None
        except Exception as e:
            print(f"查询头像失败: {e}")
            return None
        finally:
            cursor.close()